package com.ningxia.water.mapper;

import com.ningxia.water.entity.TsWellInfo;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ningxia.water.vo.out.*;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * @author hz
 * @since 2021-03-15
 */
@Mapper
public interface TsWellInfoMapper extends BaseMapper<TsWellInfo> {

    /**
     * 获取机井数量及其各种状态的数量
     *
     * @return
     */
    @Select("SELECT\n" +
            "\tcount( 1 ) AS total,\n" +
            "\tsum( CASE WHEN STATUS = 1001 THEN 1 ELSE 0 END ) as installedCounts,\n" +
            "\tsum( CASE WHEN STATUS = 1002 THEN 1 ELSE 0 END ) as unInstallCounts,\n" +
            "\tsum( CASE WHEN STATUS = 1003 THEN 1 ELSE 0 END ) as disableCounts,\n" +
            "\tsum( CASE WHEN STATUS = 4444 THEN 1 ELSE 0 END ) as exceptionCounts\n" +
            "FROM\n" +
            "\tts_well_info")
    WellStatusCountsVo getWellStatusCount();


    /**
     * 当年全部机井累计用水量
     *
     * @param startTime
     * @param endTime
     * @return
     */
    @Select("SELECT sum(md.month_data)/10000 as totalVolume from ts_month_data md where md.time BETWEEN #{startTime} and #{endTime}")
    Double getTotalConsumed(String startTime, String endTime);


    /**
     * 通过当前时间获取机井用水量排名  limit20
     *
     * @param startTime
     * @param endTime
     * @return
     */
    @Select("SELECT\n" +
            "\tai.applicant_name,\n" +
            "\tsum( md.month_data )/10000  volume \n" +
            "FROM\n" +
            "\tts_applicant_info ai\n" +
            "\tLEFT JOIN ts_applicant_well aw ON ai.applicant_id = aw.applicant_id\n" +
            "\tLEFT JOIN ts_month_data md ON aw.well_id = md.well_id\n" +
            "where md.time BETWEEN #{startTime} and #{endTime}\n" +
            "GROUP BY\n" +
            "\tai.applicant_id\n" +
            "ORDER BY\n" +
            "\tvolume DESC \n" +
            "LIMIT 20")
    List<RankConsumedVo> rankOfWaterConsumed(String startTime, String endTime);


    /**
     * 用水户年度用水量
     *
     * @param startTime
     * @param endTime
     * @return
     */
    @Select("SELECT\n" +
            "\tai.applicant_name,\n" +
            "\tsum( md.month_data )/10000  volume \n" +
            "FROM\n" +
            "\tts_applicant_info ai\n" +
            "\tLEFT JOIN ts_applicant_well aw ON ai.applicant_id = aw.applicant_id\n" +
            "\tLEFT JOIN ts_month_data md ON aw.well_id = md.well_id\n" +
            "where md.time BETWEEN #{startTime} and #{endTime}\n" +
            "GROUP BY\n" +
            "\tai.applicant_id\n" +
            "ORDER BY\n" +
            "\tvolume DESC \n")
    List<RankConsumedVo> getWaterConsumed(String startTime, String endTime);

    /**
     * 每年全部机井的每月用水量
     *
     * @param startTime
     * @param endTime
     * @return
     */
    @Select("SELECT\n" +
            "\tmd.time as month,\n" +
            "\tsum( md.month_data )/ 10000 as volume \n" +
            "FROM\n" +
            "\tts_month_data md \n" +
            "WHERE\n" +
            "\tmd.time BETWEEN #{startTime} and #{endTime} \n" +
            "GROUP BY\n" +
            "\tmd.time")
    List<MonthConsumedVo> getMonthConsumed(String startTime, String endTime);


    /**
     * 使用用水户id查询对应机井信息（用于一对多关联查询）
     *
     * @param applicantId
     * @return
     */
    @Select("SELECT wi.well_name as wellName FROM ts_applicant_well aw LEFT JOIN ts_well_info wi ON aw.well_id = wi.well_id where aw.applicant_id = #{applicantId}")
    String findByApplicantId(Integer applicantId);

    /**
     * 使用用水户id查询对应机井信息（用于一对多关联查询）
     *
     * @param applicantId
     * @return
     */
    @Select("SELECT wi.well_name as wellName FROM ts_applicant_well aw LEFT JOIN ts_well_info wi ON aw.well_id = wi.well_id where aw.applicant_id = #{applicantId} AND wi.status = 4444")
    String findWarnWellByApplicantId(Integer applicantId);

    /**
     * 获取全部机井信息
     *
     * @return
     */
    @Select("SELECT\n" +
            "\tai.applicant_id,\n" +
            "\tai.applicant_name,\n" +
            "\tai.phone,\n" +
            "\twi.well_id,\n" +
            "\twi.well_code,\n" +
            "\twi.well_type,\n" +
            "\twi.well_name,\n" +
            "\twi.zone_id,\n" +
            "\twi.zone_type,\n" +
            "\twi.status,\n" +
            "\twi.remark,\n" +
            "\twi.well_usage,\n" +
            "\twi.latitude,\n" +
            "\twi.longitude,\n" +
            "\twi.com_number,\n" +
            "\twi.irrigation_area,\n" +
            "\twi.create_time,\n" +
            "\twi.well_caliber,\n" +
            "\twi.well_depth\n" +
            "FROM\n" +
            "\tts_applicant_info ai\n" +
            "\tLEFT JOIN ts_applicant_well aw ON ai.applicant_id = aw.applicant_id\n" +
            "\tLEFT JOIN ts_well_info wi ON aw.well_id = wi.well_id")
    List<WellInfoVo> getWellInfos();

    /**
     * 根据条件查询机井信息
     *
     * @param applicantName
     * @param wellCode
     * @param wellName
     * @param wellType
     * @return
     */
    @Select("<script>" +
            "SELECT\n" +
            "\tai.applicant_id,\n" +
            "\tai.applicant_name,\n" +
            "\tai.phone,\n" +
            "\twi.well_id,\n" +
            "\twi.well_code,\n" +
            "\twi.well_type,\n" +
            "\twi.well_name,\n" +
            "\twi.zone_id,\n" +
            "\twi.zone_type,\n" +
            "\twi.status,\n" +
            "\twi.remark,\n" +
            "\twi.well_usage,\n" +
            "\twi.latitude,\n" +
            "\twi.longitude,\n" +
            "\twi.com_number,\n" +
            "\twi.irrigation_area,\n" +
            "\twi.create_time,\n" +
            "\twi.well_caliber,\n" +
            "\twi.well_depth\n" +
            "FROM\n" +
            "\tts_applicant_info ai\n" +
            "\tLEFT JOIN ts_applicant_well aw ON ai.applicant_id = aw.applicant_id\n" +
            "\tLEFT JOIN ts_well_info wi ON aw.well_id = wi.well_id" +
            "<where>" +
            " <if test='applicantName!= null'>" +
            " and ai.applicant_name like CONCAT('%',#{applicantName},'%') " +
            " </if>" +
            " <if test='wellCode!= null'>" +
            " and well_code = #{wellCode} " +
            " </if>" +
            " <if test='wellName!= null'>" +
            " and well_name like CONCAT('%',#{wellName},'%') " +
            " </if>" +
            " <if test='wellType!= null'>" +
            " and well_type =#{wellType} " +
            " </if>" +
            " <if test='status!= null'>" +
            " and `status` = #{status} " +
            " </if>" +
            " <if test='zoneType!= null'>" +
            " and zone_type = #{zoneType} " +
            " </if>" +
            " </where>" +
            "</script>")
    List<WellInfoVo> findWellInfosByConditions(String applicantName, String wellCode, String wellName, Integer wellType, Integer status, Integer zoneType);


    /**
     * 修改指定机井的状态
     *
     * @param wellId
     * @param status
     * @return
     */
    @Update("update ts_well_info set status = #{status} where well_id = #{wellId}")
    Integer updateStatus(Integer wellId, Integer status);

    /**
     * 获取机井年报数据
     *
     * @param currentTime
     * @return
     */
    @Select("SELECT wi.well_id as id, wi.well_name as name, sum(md.month_data)/10000 as volume\n" +
            "FROM ts_well_info wi\n" +
            "LEFT JOIN ts_month_data md ON wi.well_id = md.well_id and md.time BETWEEN concat(#{currentTime}, '-01') and concat(#{currentTime}, '-12')\n" +
            "GROUP BY wi.well_id")
    List<ReportVo> getWellReportByYear(String currentTime);

    /**
     * 获取机井月报数据
     *
     * @param currentTime
     * @return
     */
    @Select("SELECT wi.well_id as id, wi.well_name as name, md.month_data/10000 as volume\n" +
            "FROM ts_well_info wi\n" +
            "LEFT JOIN ts_month_data md ON wi.well_id = md.well_id and md.time = #{currentTime}")
    List<ReportVo> getWellReportByMonth(String currentTime);

    /**
     * 获取机井日报数据
     *
     * @param currentTime
     * @return
     */
    @Select("SELECT wi.well_id as id, wi.well_name as name, dd.day_data/10000 as volume\n" +
            "FROM ts_well_info wi\n" +
            "LEFT JOIN ts_day_data dd ON wi.well_id = dd.well_id and dd.time = #{currentTime}")
    List<ReportVo> getWellReportByDay(String currentTime);

    /**
     * 设置机井状态
     * @param wellId
     * @param status
     */
    @Update("update ts_well_info set status = #{status} where well_id = #{wellId}")
    void setWellStatusByWellId(Integer wellId, int status);

    /**
     * 通过wellCode获取wellId
     * @param wellCode
     * @return
     */
    @Select("select well_id from ts_well_info where well_code = #{wellCode}")
    Integer selectByWellCode(String wellCode);

    @Select("select aw.applicant_id from ts_applicant_well aw where aw.well_id = #{wellId}")
    Integer findApplicantIdByWellId(Integer wellId);
}
